﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using DTO;
namespace DAO
{
    public class DAO_PhanCongDT
    {
        public static bool deletePhanCongDTTheoMaSV(string masv)
        {
            try
            {
                SqlConnection cn = ketnoi.Cmb();
                string s = "Proc_deletePCDTTheoMaSV";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = cn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = s;
                cmd.Parameters.Add("@MaSV", SqlDbType.NVarChar).Value = masv;
                cmd.ExecuteNonQuery();
                cn.Close();
                return true;
            }
            catch (Exception e)
            {
                return false;
            }
        }
        public static bool KiemtraThemPhanCong(string makhoahoc, string maloaidt)
        {
            List<DTO_DeTai> Listdetai = new List<DTO_DeTai>();
            try
            {
                string s = "proc_KiemtraThemPhanCong";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = ketnoi.Cmb();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = s;
                cmd.Parameters.Add("@MaKH", SqlDbType.NVarChar).Value = makhoahoc;
                cmd.Parameters.Add("@MaLoaiDT", SqlDbType.NVarChar).Value = maloaidt;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                if (dt.Rows.Count > 0)
                    return true;
                return false;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        public static bool KiemtraTaoPhanCong_MaDT(string makhoahoc, string maloaidt)
        {
            List<DTO_DeTai> Listdetai = new List<DTO_DeTai>();
            try
            {
                string s = "proc_KiemtraTaoPhanCong_MaDT";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = ketnoi.Cmb();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = s;
                cmd.Parameters.Add("@MaKH", SqlDbType.NVarChar).Value = makhoahoc;
                cmd.Parameters.Add("@MaLoaiDT", SqlDbType.NVarChar).Value = maloaidt;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                if (dt.Rows.Count > 0)
                    return true;//da tao roi
                return false;// chua tao
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        public static bool KiemtraTaoPhanCong_MaCN(string makhoahoc,string maloaidt)
        {
            List<DTO_DeTai> Listdetai = new List<DTO_DeTai>();
            try
            {
                string s = "proc_KiemtraTaoPhanCong_MaCN";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = ketnoi.Cmb();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = s;
                cmd.Parameters.Add("@MaKH", SqlDbType.NVarChar).Value = makhoahoc;
                cmd.Parameters.Add("@MaLoaiDT", SqlDbType.NVarChar).Value = maloaidt;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                if (dt.Rows.Count > 0)
                    return true;// da tao roi
                return false;// chua tao
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        public static int GetIDMax_PCDT()
        {
            try
            {
                string s = "getMaxID_PCDT";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = ketnoi.Cmb();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = s;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                ketnoi.Cmb().Close();
                return int.Parse(dt.Rows[0]["ID_PCDT"].ToString());
            }
            catch (Exception ex)
            {
                return 0;
            }
        }
        public static int getCount_SinhVienHienHanh_PCDT(string madt)
        {
            try
            {
                string s = "getCount_SinhVienHienHanh_PCDT";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = ketnoi.Cmb();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = s;
                cmd.Parameters.Add("@MaDT", SqlDbType.NVarChar).Value = madt;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                ketnoi.Cmb().Close();
                return int.Parse(dt.Rows[0]["svhd"].ToString());
            }
            catch (Exception ex)
            {
                return 0;
            }
        }
        public static bool Updata_SoSVHH_Detai(string Madt)
        {
            try
            {
                SqlConnection cn = ketnoi.Cmb();
                string s = "Updata_SoSVHH_Detai";
                SqlCommand cmd1 = new SqlCommand(s, cn);
                cmd1.CommandType = CommandType.StoredProcedure;
                cmd1.Parameters.Add("@MaDT", SqlDbType.NVarChar).Value = Madt;
                if (cn.State == ConnectionState.Closed)
                    cn.Open();
                cmd1.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        public static bool Delete_PCDT_SinhVienLoaiDT(string masv,string maloaidt)
        {
            try
            {
                SqlConnection cn = ketnoi.Cmb();
                string s = "Delete_PCDT_SinhVienLoaiDT";
                SqlCommand cmd1 = new SqlCommand(s, cn);
                cmd1.CommandType = CommandType.StoredProcedure;
                cmd1.Parameters.Add("@MaSV", SqlDbType.NVarChar).Value = masv;
                cmd1.Parameters.Add("@MaloaiDT", SqlDbType.NVarChar).Value = maloaidt ;
                if (cn.State == ConnectionState.Closed)
                    cn.Open();
                cmd1.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public static bool insert_PhanCongDeTai(DTO_PhanCongDT pcdt)
        {
            try
            {
                SqlConnection cn = ketnoi.Cmb();
                string s = "insert_PhancongDT";
                SqlCommand cmd1 = new SqlCommand(s, cn);
                cmd1.CommandType = CommandType.StoredProcedure;
                cmd1.Parameters.AddWithValue("@MaDT", pcdt.MaDT);
                cmd1.Parameters.AddWithValue("@MaSV", pcdt.MaSV);
                cmd1.Parameters.AddWithValue("@MaCN", pcdt.MaCN);
                cmd1.Parameters.AddWithValue("@MaKH", pcdt.MaKH);
                cmd1.Parameters.AddWithValue("@MaLoaiDT", pcdt.MaLoaiDT);
                cmd1.Parameters.AddWithValue("@NgayNhan", pcdt.NgayNhan);
                cmd1.Parameters.AddWithValue("@NgayHetHan_ChonDT", pcdt.NgayHetHan);
          
                cmd1.Parameters.AddWithValue("@DiemNHD", pcdt.DiemNHD);
                cmd1.Parameters.AddWithValue("@DiemPB", pcdt.DiemPB);
                cmd1.Parameters.AddWithValue("@TrangThai", pcdt.TrangThai);
                if (cn.State == ConnectionState.Closed)
                    cn.Open();
                cmd1.ExecuteNonQuery();

            }
           
            catch (Exception ex)
            {
            }
            return true;
        }
        public static bool UpdateLoaidt01(DTO_PhanCongDT pcdt)
        {
            try
            {
                SqlConnection cn = ketnoi.Cmb();
                string s = "proc_UpdateLDT01";
                SqlCommand cmd1 = new SqlCommand(s, cn);
                cmd1.CommandType = CommandType.StoredProcedure;
                cmd1.Parameters.AddWithValue("@MaDT", pcdt.MaDT);
                cmd1.Parameters.AddWithValue("@MaSV", pcdt.MaSV);
                cmd1.Parameters.AddWithValue("@MaCN", pcdt.MaCN);
                cmd1.Parameters.AddWithValue("@MaLoaiDT", pcdt.MaLoaiDT);
                if (cn.State == ConnectionState.Closed)
                    cn.Open();
                cmd1.ExecuteNonQuery();
                return true;
            }

            catch (Exception ex)
            {
                return false;
            }
            
        }
       /* public static bool ChonDT01(string masv, string maloaidt) //a tính///
        {
            List<DTO_DeTai>  listDetai = DAO_DeTai.selectDeTai_TheoMaLoaiDT(maloaidt);
            DTO_PhanCongDT pcdt = new DTO_PhanCongDT();
            string makh = DAO_SinhVien.SelectSinhVienTheoMaSV(masv)[0].MaKH;
            int j = 0;
            while (listDetai[j].SoSVHH >= listDetai[j].SVToiDa)
                j++;
            if (listDetai[j].SoSVHH >= listDetai[j].SVToiDa)
                return false;
            pcdt.MaDT = listDetai[j].MaDT;
            pcdt.MaSV = masv;
            pcdt.MaCN = listDetai[j].MaCN;
            pcdt.MaKH = makh;
            pcdt.MaLoaiDT = maloaidt;
            if (!UpdateLoaidt01(pcdt))
                return false;
            return true;

        }*/
       
        
       public static bool ChonDT01(string masv, string maloaidt)////cua tiny tinh
        {
            List<DTO_PhanCongDT> listPCDT = new List<DTO_PhanCongDT>();
            List<DTO_DeTai>  listDetai = DAO_DeTai.selectDeTai_TheoMaLoaiDT(maloaidt);
            DTO_PhanCongDT pcdt = new DTO_PhanCongDT();
            listPCDT = Select_PhanCongDeTai_MaSV_LoaiDT(masv,maloaidt);
                for (int i = 0; i < listPCDT.Count; i++)
                {
                    string makh = DAO_SinhVien.SelectSinhVienTheoMaSV(masv)[0].MaKH;
                    int j = 0;
                    while (listDetai[j].SoSVHH >= listDetai[j].SVToiDa)
                        j++;
                    if (listDetai[j].SoSVHH >= listDetai[j].SVToiDa)
                        return false;
                    pcdt.MaDT = listDetai[j].MaDT;
                    pcdt.MaSV = masv;
                    pcdt.MaCN = listDetai[j].MaCN;
                    pcdt.MaKH = makh;
                    pcdt.MaLoaiDT = maloaidt;
                    string madt = listDetai[j].MaDT;
                    string macn = listDetai[j].MaCN;
                    int id = listPCDT[i].ID_PCDT;
                    if (listPCDT[i].DiemNHD == -1 && listPCDT[i].DiemPB == -1)
                    {
                        if (!UpdateLDT01(id, madt,macn))
                        {
                            return false;
                        }
                        Delete_PCDT_SinhVienLoaiDT(masv, maloaidt);
                        return true;
                    }
            }
          
            return true;
        }
        public static bool UpdateLDT01(int id, string Madt, string macn)
        {
            try
            {
                SqlConnection cn = ketnoi.Cmb();
                string s = "proc_UpdateLDT01";
                SqlCommand cmd1 = new SqlCommand(s, cn);
                cmd1.CommandType = CommandType.StoredProcedure;
                cmd1.Parameters.Add("@ID_PCDT", SqlDbType.NVarChar).Value = id;
                cmd1.Parameters.Add("@MaDT", SqlDbType.NVarChar).Value = Madt;
                cmd1.Parameters.Add("@MaCN", SqlDbType.NVarChar).Value = macn;
                if (cn.State == ConnectionState.Closed)
                    cn.Open();
                cmd1.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        /// <summary>
        /// /////////////////////////////////
        /// </summary>
        /// <param name="maloaidt"></param>
        /// <param name="makhoahoc"></param>
        /// <returns></returns>
        public static bool PhanCongDeTai_LDT01(string maloaidt, string makhoahoc)
        {
            try
            {
                List<DTO_DeTai> listDetai = new List<DTO_DeTai>();
                List<DTO_SinhVien> listSinhVien = new List<DTO_SinhVien>();
                listDetai = DAO_DeTai.selectDeTai_TheoMaLoaiDT(maloaidt);
                listSinhVien = DAO_SinhVien.SelectSinhVienTheoKhoaHoc(makhoahoc);
                int i = 0;int j = 0;
                while (i < listSinhVien.Count)
                {
                    if (j >= listDetai.Count)
                        j = 0;
                    while (listDetai[j].SoSVHH >= listDetai[j].SVToiDa)
                        j++;
                    DTO_PhanCongDT pcdt = new DTO_PhanCongDT();
                    pcdt.MaDT = "";
                    pcdt.MaSV = listSinhVien[i].MaSV;
                    pcdt.MaCN = "";
                    pcdt.MaKH = makhoahoc;
                    pcdt.MaLoaiDT = listDetai[j].MaLoaiDT;
                    pcdt.NgayNhan = DateTime.Parse(DateTime.Now.ToString("yyyy/MM/dd"));
                    pcdt.NgayHetHan = DateTime.Parse(DateTime.Now.ToString("yyyy/MM/dd"));
                 
                    pcdt.DiemNHD = -1;
                    pcdt.DiemPB = -1;
                    pcdt.TrangThai = true;
                    if (!insert_PhanCongDeTai(pcdt))
                        return false;
                    else
                        Updata_SoSVHH_Detai(pcdt.MaDT);
                   // j++;
                    i++;
                }
                return true;
            }
            catch { return false; };
        }

        //Din
        public static bool PhanCongTudong_Chon_LD01(string maloaidt, string masv)
        {
            try
            {
                List<DTO_DeTai> listDetai = new List<DTO_DeTai>();
                listDetai = DAO_DeTai.selectDeTai_TheoMaLoaiDT(maloaidt);
                int j = 0;
                if (j >= listDetai.Count)
                        j = 0;
                    while (listDetai[j].SoSVHH >= listDetai[j].SVToiDa)
                        j++;
                if(listDetai[j].SoSVHH >= listDetai[j].SVToiDa)
                    return false;

                    DTO_PhanCongDT pcdt = new DTO_PhanCongDT();

                    pcdt.MaDT = listDetai[j].MaDT;
                    pcdt.MaCN = listDetai[j].MaCN;
                    pcdt.MaLoaiDT = listDetai[j].MaLoaiDT;
                    pcdt.MaKH = DAO_SinhVien.SelectSinhVienTheoMaSV(masv)[0].MaKH;
                    pcdt.MaSV=masv;
                    pcdt.NgayNhan = DateTime.Parse(DateTime.Now.ToString("yyyy/MM/dd"));
                    pcdt.NgayHetHan = DateTime.Parse(DateTime.Now.ToString("yyyy/MM/dd"));
                 
                    pcdt.DiemNHD = -1;
                    pcdt.DiemPB = -1;
                    pcdt.TrangThai = true;
                    if (!insert_PhanCongDeTai(pcdt))
                        return false;
                    else
                        Updata_SoSVHH_Detai(pcdt.MaDT);
                    j++;

                return true;

            }
            catch { return false; };
        }
        public static List<DTO_PhanCongDT> SelectSinhVien_TrongPhanCong(string masv, string maloaidt)
        {
            List<DTO_PhanCongDT> listPhancongdt = new List<DTO_PhanCongDT>();
            try
            {
                string s = "Prc_SelectSinhVien_TrongPhanCong";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = ketnoi.Cmb();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = s;
                cmd.Parameters.Add("@MaSV", SqlDbType.NVarChar).Value = masv;
                cmd.Parameters.Add("@MaLoaiDT", SqlDbType.NVarChar).Value = maloaidt;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DTO_PhanCongDT pcdt = new DTO_PhanCongDT();
                    pcdt.ID_PCDT = int.Parse(dt.Rows[i]["ID_PCDT"].ToString());
                    pcdt.MaDT = dt.Rows[i]["MaDT"].ToString();
                    pcdt.MaSV = dt.Rows[i]["MaSV"].ToString();
                    pcdt.MaCN = dt.Rows[i]["MaCN"].ToString();
                    pcdt.MaKH = dt.Rows[i]["MaKH"].ToString();
                    pcdt.MaLoaiDT = dt.Rows[i]["MaLoaiDT"].ToString();
                    pcdt.NgayNhan = DateTime.Parse(dt.Rows[i]["NgayNhan"].ToString());
                    pcdt.NgayHetHan = DateTime.Parse(dt.Rows[i]["NgayHetHan_ChonDT"].ToString());
                    pcdt.DiemNHD = float.Parse(dt.Rows[i]["DiemNHD"].ToString());
                    pcdt.DiemPB = float.Parse(dt.Rows[i]["DiemPB"].ToString());

                    listPhancongdt.Add(pcdt);
                }
                ketnoi.Cmb().Close();
                return listPhancongdt;
            }
            catch (Exception ex)
            {
                return listPhancongdt;
            }
        }

        //
        public static List<DTO_PhanCongDT> SelectSinhVien_TrongPhanCong_LDT02(string masv,string macn, string maloaidt)
        {
            List<DTO_PhanCongDT> listPhancongdt = new List<DTO_PhanCongDT>();
            try
            {
                string s = "Prc_SelectSinhVien_TrongPhanCong_LDT02";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = ketnoi.Cmb();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = s;
                cmd.Parameters.Add("@MaSV", SqlDbType.NVarChar).Value = masv;
                cmd.Parameters.Add("@MaCN", SqlDbType.NVarChar).Value = macn;
                cmd.Parameters.Add("@MaLoaiDT", SqlDbType.NVarChar).Value = maloaidt;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DTO_PhanCongDT pcdt = new DTO_PhanCongDT();
                    pcdt.ID_PCDT = int.Parse(dt.Rows[i]["ID_PCDT"].ToString());
                    pcdt.MaDT = dt.Rows[i]["MaDT"].ToString();
                    pcdt.MaSV = dt.Rows[i]["MaSV"].ToString();
                    pcdt.MaCN = dt.Rows[i]["MaCN"].ToString();
                    pcdt.MaKH = dt.Rows[i]["MaKH"].ToString();
                    pcdt.MaLoaiDT = dt.Rows[i]["MaLoaiDT"].ToString();
                    pcdt.NgayNhan = DateTime.Parse(dt.Rows[i]["NgayNhan"].ToString());
                    pcdt.NgayHetHan = DateTime.Parse(dt.Rows[i]["NgayHetHan_ChonDT"].ToString());
                    pcdt.DiemNHD = float.Parse(dt.Rows[i]["DiemNHD"].ToString());
                    pcdt.DiemPB = float.Parse(dt.Rows[i]["DiemPB"].ToString());

                    listPhancongdt.Add(pcdt);
                }
                ketnoi.Cmb().Close();
                return listPhancongdt;
            }
            catch (Exception ex)
            {
                return listPhancongdt;
            }
        }
        /////////////////////////
        //end
        public static bool ThemPhanCongDeTai_LDT02(string makhoahoc,string maloaidt, DateTime ngayhethan)
        {
            try
            {
                List<DTO_SinhVien> listSinhVien = new List<DTO_SinhVien>();
                listSinhVien = DAO_SinhVien.SelectSinhVienTheoKhoaHoc(makhoahoc);
                int i = 0; 
                while (i < listSinhVien.Count)
                {
                    DTO_PhanCongDT pcdt = new DTO_PhanCongDT();
                    pcdt.MaDT = "";
                    pcdt.MaSV = listSinhVien[i].MaSV;
                    pcdt.MaCN = "";
                    pcdt.MaKH = makhoahoc;
                    pcdt.MaLoaiDT = maloaidt;
                    pcdt.NgayNhan = DateTime.Parse(DateTime.Now.ToString("yyyy/MM/dd"));
                    pcdt.NgayHetHan = ngayhethan;
                    pcdt.DiemNHD = -1;
                    pcdt.DiemPB = -1;
                    pcdt.TrangThai = true;
                    if (!insert_PhanCongDeTai(pcdt))
                    { }
                    else
                        Updata_SoSVHH_Detai(pcdt.MaDT);
                    i++;
                }
                return true;
            }
            catch { return false; };
        }
        public static DateTime getNgayhethan(string maloaidt)
        {
            DateTime ngayhethan = new DateTime();
            try
            {
                string s = "Prc_NgayHetHan";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = ketnoi.Cmb();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = s;
                cmd.Parameters.Add("@MaLoaiDT", SqlDbType.NVarChar).Value = maloaidt;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                ngayhethan = DateTime.Parse(dt.Rows[0]["NgayHetHan"].ToString());
                return ngayhethan;
            }
            catch (Exception ex)
            {
                return ngayhethan;
            }
        }
        public static bool ThemPhanCongDeTai_LDT02_new(string masv, string maloaidt,string macn)//tiny tinh
        {
            try
            {
                    DateTime ngayhethan = getNgayhethan(maloaidt);
                    DTO_PhanCongDT pcdt = new DTO_PhanCongDT();
                    pcdt.MaDT = "";
                    pcdt.MaSV = masv;
                    string makhoahoc = DAO_SinhVien.SelectSinhVienTheoMaSV(masv)[0].MaKH;
                    pcdt.MaCN = "";
                    pcdt.MaKH = makhoahoc;
                    pcdt.MaLoaiDT = maloaidt;
                    pcdt.NgayNhan = DateTime.Parse(DateTime.Now.ToString("yyyy/MM/dd"));
                    pcdt.NgayHetHan = ngayhethan;
                    pcdt.DiemNHD = -1;
                    pcdt.DiemPB = -1;
                    pcdt.TrangThai = true;
                    if (!insert_PhanCongDeTai(pcdt))
                    { }
                    else
                    {
                        Updata_SoSVHH_Detai(pcdt.MaDT);
                        return TaoPhanCongTuDongDeTai_LDT02_new(maloaidt, masv, macn);
                    }
               
                return true;
            }
            catch { return false; };
        }
        /////////////////////////////////////////////////////////////////////////////////
        public static bool ThemPhanCongDeTai_LDT03(string maloaidt, string masv, string madt)
        {
            try
            {
                List<DTO_DeTai> listDetai = new List<DTO_DeTai>();
                listDetai = DAO_DeTai.selectDeTai_TheoMaLoaiDT(maloaidt);
                int j = 0;
                if (j >= listDetai.Count)
                    j = 0;
                while (listDetai[j].SoSVHH >= listDetai[j].SVToiDa)
                    j++;
                if (listDetai[j].SoSVHH >= listDetai[j].SVToiDa)
                    return false;

                DTO_PhanCongDT pcdt = new DTO_PhanCongDT();

                pcdt.MaDT = madt;
                pcdt.MaCN = listDetai[j].MaCN;
                pcdt.MaLoaiDT = listDetai[j].MaLoaiDT;
                pcdt.MaKH = DAO_SinhVien.SelectSinhVienTheoMaSV(masv)[0].MaKH;
                pcdt.MaSV = masv;
                pcdt.NgayNhan = DateTime.Parse(DateTime.Now.ToString("yyyy/MM/dd"));
                pcdt.NgayHetHan = DateTime.Parse(DateTime.Now.ToString("yyyy/MM/dd"));
                pcdt.DiemNHD = -1;
                pcdt.DiemPB = -1;
                pcdt.TrangThai = true;
                if (!insert_PhanCongDeTai(pcdt))
                    return false;
                else
                    Updata_SoSVHH_Detai(pcdt.MaDT);
                j++;

                return true;

            }
            catch { return false; };
        }
        ////////////////////////////////////////////////
        
        /////////
        /// </summary>
        /// <param name="makh"></param>
        /// <param name="maloaidt"></param>
        /// <returns></returns>
        public static List<DTO_PhanCongDT> select_PhanCongDeTai_TheoMaKH_TheoMaLoaiDT(string makh, string maloaidt)
        {
            List<DTO_PhanCongDT> listPhancongdt = new List<DTO_PhanCongDT>();
            try
            {
                string s = "proc_SelectPhanCongDetai_TheoMaKH_ThemMaLoaiDT";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = ketnoi.Cmb();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = s;
                cmd.Parameters.Add("@MaKH", SqlDbType.NVarChar).Value = makh;
                cmd.Parameters.Add("@MaLoaiDT", SqlDbType.NVarChar).Value = maloaidt;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DTO_PhanCongDT pcdt = new DTO_PhanCongDT();
                    pcdt.ID_PCDT = int.Parse(dt.Rows[i]["ID_PCDT"].ToString());
                    pcdt.MaDT = dt.Rows[i]["MaDT"].ToString();
                    pcdt.MaSV = dt.Rows[i]["MaSV"].ToString();
                    pcdt.MaCN = dt.Rows[i]["MaCN"].ToString();
                    pcdt.MaKH = dt.Rows[i]["MaKH"].ToString();
                    pcdt.MaLoaiDT = dt.Rows[i]["MaLoaiDT"].ToString();
                    pcdt.NgayNhan = DateTime.Parse(dt.Rows[i]["NgayNhan"].ToString());
                    pcdt.NgayHetHan = DateTime.Parse(dt.Rows[i]["NgayHetHan_ChonDT"].ToString());
                    pcdt.DiemNHD = float.Parse(dt.Rows[i]["DiemNHD"].ToString());
                    pcdt.DiemPB = float.Parse(dt.Rows[i]["DiemPB"].ToString());
                    pcdt.TrangThai = bool.Parse(dt.Rows[i]["TrangThai"].ToString());
                    listPhancongdt.Add(pcdt);
                }
                ketnoi.Cmb().Close();
                return listPhancongdt;
            }
            catch (Exception ex)
            {
                return listPhancongdt;
            }
        }
        public static bool UpdateMaDT_PhanCongDT_LDT02(int id,string Madt)
        {
            try
            {
                SqlConnection cn = ketnoi.Cmb();
                string s = "Updata_MaDT_PhanCongDT";
                SqlCommand cmd1 = new SqlCommand(s, cn);
                cmd1.CommandType = CommandType.StoredProcedure;
                cmd1.Parameters.Add("@ID_PCDT", SqlDbType.NVarChar).Value = id;
                cmd1.Parameters.Add("@MaDT", SqlDbType.NVarChar).Value = Madt;
                if (cn.State == ConnectionState.Closed)
                    cn.Open();
                cmd1.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        public static bool UpdateMaDT_PhanCongDT_LDT02_new(int id, string Madt, string macn)
        {
            try
            {
                SqlConnection cn = ketnoi.Cmb();
                string s = "Updata_MaDT_PhanCongDT_new";
                SqlCommand cmd1 = new SqlCommand(s, cn);
                cmd1.CommandType = CommandType.StoredProcedure;
                cmd1.Parameters.Add("@ID_PCDT", SqlDbType.NVarChar).Value = id;
                cmd1.Parameters.Add("@MaDT", SqlDbType.NVarChar).Value = Madt;
                cmd1.Parameters.Add("@MaCN", SqlDbType.NVarChar).Value = macn;
                if (cn.State == ConnectionState.Closed)
                    cn.Open();
                cmd1.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        public static bool Updata_MaDT_PhanCongDT_03( string Madt)
        {
            try
            {
                SqlConnection cn = ketnoi.Cmb();
                string s = "Updata_MaDT_PhanCongDT_new";
                SqlCommand cmd1 = new SqlCommand(s, cn);
                cmd1.CommandType = CommandType.StoredProcedure;
                    cmd1.Parameters.Add("@MaDT", SqlDbType.NVarChar).Value = Madt;
              
                if (cn.State == ConnectionState.Closed)
                    cn.Open();
                cmd1.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        /// <summary>
        /// ///////////////////////////////////////
        /// </summary>
        /// <param name="maloaidt"></param>
        /// <param name="makhoahoc"></param>
        /// <returns></returns>
        public static bool TaoPhanCongTuDongDeTai_LDT02(string maloaidt, string makhoahoc)//huyenkute
        {
            try
            {
                List<DTO_PhanCongDT> listPCDT = new List<DTO_PhanCongDT>();
                listPCDT = select_PhanCongDeTai_TheoMaKH_TheoMaLoaiDT(makhoahoc, maloaidt);
                for (int i = 0; i < listPCDT.Count; i++)
                {
                    List<DTO_DeTai> listdt = new List<DTO_DeTai>();
                    listdt =  DAO_DeTai.selectDeTai_TheoMaCN(listPCDT[i].MaCN,maloaidt);
                    int j  = 0;
                    try
                    {
                        while (listdt[j].SoSVHH >= listdt[j].SVToiDa)
                            j++;

                        string madt = listdt[j].MaDT;
                        int id = listPCDT[i].ID_PCDT;
                        if (!UpdateMaDT_PhanCongDT_LDT02(id, madt))
                        { }
                    }
                    catch { }
                }
                return true;
            }
            catch { return false; };
        }
        public static bool ChonDT03(string masv, string maloaidt, string madt)
        {
            List<DTO_PhanCongDT> listPCDT = new List<DTO_PhanCongDT>();
            List<DTO_DeTai> listDetai = DAO_DeTai.selectDeTai_TheoMaLoaiDT(maloaidt);
            DTO_PhanCongDT pcdt = new DTO_PhanCongDT();
            listPCDT = Select_PhanCongDeTai_MaSV_LoaiDT(masv, maloaidt);
            for (int i = 0; i < listPCDT.Count; i++)
            {
                string makh = DAO_SinhVien.SelectSinhVienTheoMaSV(masv)[0].MaKH;
                int j = 0;
                while (listDetai[j].SoSVHH >= listDetai[j].SVToiDa)
                    j++;
                if (listDetai[j].SoSVHH >= listDetai[j].SVToiDa)
                    return false;
                pcdt.MaDT = madt;
                pcdt.MaSV = masv;
                pcdt.MaCN = listDetai[j].MaCN;
                pcdt.MaKH = makh;
                pcdt.MaLoaiDT = maloaidt;
              
                string macn = listDetai[j].MaCN;
                int id = listPCDT[i].ID_PCDT;
                if (listPCDT[i].DiemNHD == -1 && listPCDT[i].DiemPB == -1)
                {
                    if (!UpdateMaDT_PhanCongDT_LDT02_new(id, madt, macn))
                    {
                        return false;
                    }
                    Delete_PCDT_SinhVienLoaiDT(masv, maloaidt);
                    return true;
                }
            }

            return true;
        }
        /////////////////////////////////////
        public static bool TaoPhanCongTuDongDeTai_LDT02_new(string maloaidt,string masv, string macn)
        {
            try
            {
                List<DTO_PhanCongDT> listPCDT = new List<DTO_PhanCongDT>();
                string makh = DAO_SinhVien.SelectSinhVienTheoMaSV(masv)[0].MaKH;
                listPCDT = select_PhanCongDeTai_TheoMaKH_TheoMaLoaiDT(makh, maloaidt);
                for (int i = 0; i < listPCDT.Count; i++)
                {
                    if (listPCDT[i].MaSV == masv && listPCDT[i].MaLoaiDT == maloaidt)
                    {
                        List<DTO_DeTai> listdt = new List<DTO_DeTai>();
                        listdt = DAO_DeTai.selectDeTai_TheoMaCN(macn, maloaidt);
                        int j = 0;
                        try
                        {
                            while (listdt[j].SoSVHH >= listdt[j].SVToiDa)
                                j++;
                            string madt = listdt[j].MaDT;
                            int id = listPCDT[i].ID_PCDT;
                            if (listPCDT[i].DiemNHD == -1 && listPCDT[i].DiemPB == -1)
                            {
                                if (!UpdateMaDT_PhanCongDT_LDT02_new(id, madt, macn))
                                {
                                    return false;
                                }
                                Delete_PCDT_SinhVienLoaiDT(masv, maloaidt);
                                return true;
                            }
                        }
                        catch { }
                    }
                    else
                        continue;
                }
                return false;
            }
            catch { return false; };
        }
      
        
        ///////////////////////////////////////////////////////////////////
       
        /// <summary>
        /// ////////////////////
        /// </summary>
        /// <param name="madt"></param>
        /// <returns></returns>
        public static List<DTO_PhanCongDT> select_PhanCongDeTai_Nhom_MaDT(string madt)
        {
            List<DTO_PhanCongDT> listPhancongdt = new List<DTO_PhanCongDT>();
            try
            {
                string s = "proc_SelectDetai_Nhom_TheoMaDT";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = ketnoi.Cmb();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = s;
                cmd.Parameters.Add("@MaDT", SqlDbType.NVarChar).Value = madt;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DTO_PhanCongDT pcdt = new DTO_PhanCongDT();
                    pcdt.ID_PCDT = int.Parse(dt.Rows[i]["ID_PCDT"].ToString());
                    pcdt.MaDT = dt.Rows[i]["MaDT"].ToString();
                    pcdt.MaSV = dt.Rows[i]["MaSV"].ToString();
                    pcdt.TenSV = dt.Rows[i]["TenSV"].ToString();
                    pcdt.TenLop = dt.Rows[i]["TenLop"].ToString();
                    listPhancongdt.Add(pcdt);
                }
                ketnoi.Cmb().Close();
                return listPhancongdt;
            }
            catch (Exception ex)
            {
                return listPhancongdt;
            }
        }
        public static bool Update_Nhom_TheoMaDT_MaCN_Nhom( string madt, string macn, string nhom, string masv, string maloaidt)
        {
            try
            {
                SqlConnection cn = ketnoi.Cmb();
                string s = "Proc_Update_Nhom_TheoMaDT_MaCN_Nhom";
                SqlCommand cmd1 = new SqlCommand(s, cn);
                cmd1.CommandType = CommandType.StoredProcedure;
                cmd1.Parameters.Add("@MaDT", SqlDbType.NVarChar).Value = madt;
                cmd1.Parameters.Add("@MaCN", SqlDbType.NVarChar).Value = macn;
                cmd1.Parameters.Add("@MaSV", SqlDbType.NVarChar).Value = masv;
                cmd1.Parameters.Add("@MaLoaiDT", SqlDbType.NVarChar).Value = maloaidt;
                if (cn.State == ConnectionState.Closed)
                    cn.Open();
                cmd1.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        public static bool Update_NhomMoi_TheoMaDT_MaCN(string madt, string macn, string masv, string maloaidt)
        {
            try
            {
                SqlConnection cn = ketnoi.Cmb();
                string s = "Proc_Update_Nhom_TheoMaDT_MaCN";
                SqlCommand cmd1 = new SqlCommand(s, cn);
                cmd1.CommandType = CommandType.StoredProcedure;
                cmd1.Parameters.Add("@MaDT", SqlDbType.NVarChar).Value = madt;
                cmd1.Parameters.Add("@MaCN", SqlDbType.NVarChar).Value = macn;
                cmd1.Parameters.Add("@MaSV", SqlDbType.NVarChar).Value = masv;
                cmd1.Parameters.Add("@MaLoaiDT", SqlDbType.NVarChar).Value = maloaidt;
                if (cn.State == ConnectionState.Closed)
                    cn.Open();
                cmd1.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        public static bool Update_SV_TheoMaDT_MaCN(string macn, string masv, string maloaidt)
        {
            try
            {
                SqlConnection cn = ketnoi.Cmb();
                string s = "Proc_Update_SV_TheoMaDT_MaCN";
                SqlCommand cmd1 = new SqlCommand(s, cn);
                cmd1.CommandType = CommandType.StoredProcedure;
                cmd1.Parameters.Add("@MaCN", SqlDbType.NVarChar).Value = macn;
                cmd1.Parameters.Add("@MaSV", SqlDbType.NVarChar).Value = masv;
                cmd1.Parameters.Add("@MaLoaiDT", SqlDbType.NVarChar).Value = maloaidt;
                if (cn.State == ConnectionState.Closed)
                    cn.Open();
                cmd1.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        public static bool KiemTra_MaDT_LoaiDT(string madt, string masv, string maloaidt)
        {
            try
            {
                string s = "Proc_Update_MaDT_LoaiDT";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = ketnoi.Cmb();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = s;
                cmd.Parameters.Add("@MaDT", SqlDbType.NVarChar).Value = madt;
                cmd.Parameters.Add("@MaSV", SqlDbType.NVarChar).Value = masv;
                cmd.Parameters.Add("@MaLoaiDT", SqlDbType.NVarChar).Value = maloaidt;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                ketnoi.Cmb().Close();
                if (dt.Rows.Count >= 1)
                    return true;
                return false;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

       
        public static bool Update_MaCN_TheoLDT02(string macn, string masv, string maloaidt)
        {
            try
            {
                SqlConnection cn = ketnoi.Cmb();
                string s = "Proc_Update_MaCN_TheoLDT02";
                SqlCommand cmd1 = new SqlCommand(s, cn);
                cmd1.CommandType = CommandType.StoredProcedure;
                cmd1.Parameters.Add("@MaCN", SqlDbType.NVarChar).Value = macn;
                cmd1.Parameters.Add("@MaSV", SqlDbType.NVarChar).Value = masv;
                cmd1.Parameters.Add("@MaLoaiDT", SqlDbType.NVarChar).Value = maloaidt;
                if (cn.State == ConnectionState.Closed)
                    cn.Open();
                cmd1.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        
        public static List<DTO_PhanCongDT> Select_PhanCongDeTai_MaSV_LoaiDT(string masv, string maloaidt)
        {
            List<DTO_PhanCongDT> listPhancongdt = new List<DTO_PhanCongDT>();
            try
            {
                string s = "Proc_Select_PhanCongDeTai_MaSV_LoaiDT";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = ketnoi.Cmb();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = s;
                cmd.Parameters.Add("@MaSV", SqlDbType.NVarChar).Value = masv;
                cmd.Parameters.Add("@MaLoaiDT", SqlDbType.NVarChar).Value = maloaidt;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DTO_PhanCongDT pcdt = new DTO_PhanCongDT();
                    pcdt.ID_PCDT = int.Parse(dt.Rows[i]["ID_PCDT"].ToString());
                    pcdt.MaDT = dt.Rows[i]["MaDT"].ToString();
                    pcdt.MaSV = dt.Rows[i]["MaSV"].ToString();
                    pcdt.MaCN = dt.Rows[i]["MaCN"].ToString();
                    pcdt.MaKH = dt.Rows[i]["MaKH"].ToString();
                    pcdt.MaLoaiDT = dt.Rows[i]["MaLoaiDT"].ToString();
                    pcdt.NgayNhan = DateTime.Parse(dt.Rows[i]["NgayNhan"].ToString());
                    pcdt.NgayHetHan = DateTime.Parse(dt.Rows[i]["NgayHetHan_ChonDT"].ToString());
                    pcdt.DiemNHD = float.Parse(dt.Rows[i]["DiemNHD"].ToString());
                    pcdt.DiemPB = float.Parse(dt.Rows[i]["DiemPB"].ToString());
                    pcdt.TrangThai = bool.Parse(dt.Rows[i]["TrangThai"].ToString());
                    listPhancongdt.Add(pcdt);
                }
                ketnoi.Cmb().Close();
                return listPhancongdt;
            }
            catch (Exception ex)
            {
                return listPhancongdt;
            }
        }

        public static bool Kiemtra_PCDeTai_MaSV_MaCN(string masv, string macn ,string maloaidt)
        {
            try
            {
                string s = "Proc_Kiemtra_PCDeTai_MaSV_MaCN";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = ketnoi.Cmb();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = s;
                cmd.Parameters.Add("@MaSV", SqlDbType.NVarChar).Value = masv;
                cmd.Parameters.Add("@MaCN", SqlDbType.NVarChar).Value = macn;
                cmd.Parameters.Add("@MaLoaiDT", SqlDbType.NVarChar).Value = maloaidt;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                ketnoi.Cmb().Close();
                if (dt.Rows.Count >= 1)
                    return true;
                return false;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        public static List<DTO_PhanCongDT> PCDT_Select_MaSV_DaLam(string masv)
        {
            List<DTO_PhanCongDT> listPhancongdt = new List<DTO_PhanCongDT>();
            try
            {
                string s = "Proc_PCDT_Select_MaSV_DaLam";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = ketnoi.Cmb();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = s;
                cmd.Parameters.Add("@MaSV", SqlDbType.NVarChar).Value = masv;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DTO_PhanCongDT pcdt = new DTO_PhanCongDT();
                    pcdt.ID_PCDT = int.Parse(dt.Rows[i]["ID_PCDT"].ToString());
                    pcdt.MaDT = dt.Rows[i]["MaDT"].ToString();
                    pcdt.MaSV = dt.Rows[i]["MaSV"].ToString();
                    pcdt.MaCN = dt.Rows[i]["MaCN"].ToString();
                    pcdt.MaKH = dt.Rows[i]["MaKH"].ToString();
                    pcdt.MaLoaiDT = dt.Rows[i]["MaLoaiDT"].ToString();
                    pcdt.NgayNhan = DateTime.Parse(dt.Rows[i]["NgayNhan"].ToString());
                    pcdt.NgayHetHan = DateTime.Parse(dt.Rows[i]["NgayHetHan_ChonDT"].ToString());
                    pcdt.DiemNHD = float.Parse(dt.Rows[i]["DiemNHD"].ToString());
                    pcdt.DiemPB = float.Parse(dt.Rows[i]["DiemPB"].ToString());
                    pcdt.DiemTB = (float)((pcdt.DiemNHD * 40) / 100) + (float)((pcdt.DiemPB * 60) / 100);
                    pcdt.TrangThai = bool.Parse(dt.Rows[i]["TrangThai"].ToString());
                    pcdt.TenDT = dt.Rows[i]["TenDT"].ToString();
                    pcdt.TenGV = dt.Rows[i]["TenGV"].ToString();
                    pcdt.TenCN = dt.Rows[i]["TenCN"].ToString();
                    pcdt.TenloaiDT = dt.Rows[i]["TenloaiDT"].ToString();
                    pcdt.NamBD = dt.Rows[i]["NamBD"].ToString();
                    pcdt.NamKT = dt.Rows[i]["NamKT"].ToString();
                    pcdt.Sotuan = int.Parse(dt.Rows[i]["SoTuan"].ToString());
                    listPhancongdt.Add(pcdt);
                }
                ketnoi.Cmb().Close();
                return listPhancongdt;
            }
            catch (Exception ex)
            {
                return listPhancongdt;
            }
        }
        public static List<DTO_PhanCongDT> PCDT_Select_MaSV_DangLam(string masv)
        {
            List<DTO_PhanCongDT> listPhancongdt = new List<DTO_PhanCongDT>();
            try
            {
                string s = "Proc_PCDT_Select_MaSV_DangLam";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = ketnoi.Cmb();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = s;
                cmd.Parameters.Add("@MaSV", SqlDbType.NVarChar).Value = masv;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DTO_PhanCongDT pcdt = new DTO_PhanCongDT();
                    pcdt.ID_PCDT = int.Parse(dt.Rows[i]["ID_PCDT"].ToString());
                    pcdt.MaDT = dt.Rows[i]["MaDT"].ToString();
                    pcdt.MaSV = dt.Rows[i]["MaSV"].ToString();
                    pcdt.MaCN = dt.Rows[i]["MaCN"].ToString();
                    pcdt.MaKH = dt.Rows[i]["MaKH"].ToString();
                    pcdt.MaLoaiDT = dt.Rows[i]["MaLoaiDT"].ToString();
                    pcdt.NgayNhan = DateTime.Parse(dt.Rows[i]["NgayNhan"].ToString());
                    pcdt.NgayHetHan = DateTime.Parse(dt.Rows[i]["NgayHetHan_ChonDT"].ToString());
                    pcdt.DiemNHD = float.Parse(dt.Rows[i]["DiemNHD"].ToString());
                    pcdt.DiemPB = float.Parse(dt.Rows[i]["DiemPB"].ToString());
                    pcdt.TrangThai = bool.Parse(dt.Rows[i]["TrangThai"].ToString());
                    pcdt.TenDT = dt.Rows[i]["TenDT"].ToString();
                    pcdt.TenGV = dt.Rows[i]["TenGV"].ToString();
                    pcdt.TenCN = dt.Rows[i]["TenCN"].ToString();
                    pcdt.TenloaiDT = dt.Rows[i]["TenloaiDT"].ToString();
                    pcdt.NamBD = dt.Rows[i]["NamBD"].ToString();
                    pcdt.NamKT = dt.Rows[i]["NamKT"].ToString();
                    pcdt.Sotuan = int.Parse( dt.Rows[i]["SoTuan"].ToString());
                    listPhancongdt.Add(pcdt);
                }
                ketnoi.Cmb().Close();
                return listPhancongdt;
            }
            catch (Exception ex)
            {
                return listPhancongdt;
            }
        }

        public static List<DTO_PhanCongDT> PCDT_Select_MaGV_QLy_SVDangLam(string magv)
        {
            List<DTO_PhanCongDT> listPhancongdt = new List<DTO_PhanCongDT>();
            try
            {
                string s = "Proc_PCDT_Select_MaGV_QLy_SVDangLam";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = ketnoi.Cmb();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = s;
                cmd.Parameters.Add("@MaGV", SqlDbType.NVarChar).Value = magv;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DTO_PhanCongDT pcdt = new DTO_PhanCongDT();
                    pcdt.ID_PCDT = int.Parse(dt.Rows[i]["ID_PCDT"].ToString());
                    pcdt.MaDT = dt.Rows[i]["MaDT"].ToString();
                    pcdt.MaSV = dt.Rows[i]["MaSV"].ToString();
                    pcdt.MaCN = dt.Rows[i]["MaCN"].ToString();
                    pcdt.MaKH = dt.Rows[i]["MaKH"].ToString();
                    pcdt.MaLoaiDT = dt.Rows[i]["MaLoaiDT"].ToString();
                    pcdt.NgayNhan = DateTime.Parse(dt.Rows[i]["NgayNhan"].ToString());
                    pcdt.NgayHetHan = DateTime.Parse(dt.Rows[i]["NgayHetHan_ChonDT"].ToString());
                    pcdt.DiemNHD = float.Parse(dt.Rows[i]["DiemNHD"].ToString());
                    pcdt.DiemPB = float.Parse(dt.Rows[i]["DiemPB"].ToString());
                    pcdt.TrangThai = bool.Parse(dt.Rows[i]["TrangThai"].ToString());
                    pcdt.TenDT = dt.Rows[i]["TenDT"].ToString();
                    pcdt.TenGV = dt.Rows[i]["TenGV"].ToString();
                    pcdt.TenCN = dt.Rows[i]["TenCN"].ToString();
                    pcdt.TenloaiDT = dt.Rows[i]["TenloaiDT"].ToString();
                    pcdt.NamBD = dt.Rows[i]["NamBD"].ToString();
                    pcdt.NamKT = dt.Rows[i]["NamKT"].ToString();
                    pcdt.Sotuan = int.Parse(dt.Rows[i]["SoTuan"].ToString());
                    
                    listPhancongdt.Add(pcdt);
                }
                ketnoi.Cmb().Close();
                return listPhancongdt;
            }
            catch (Exception ex)
            {
                return listPhancongdt;
            }
        }
        public static List<DTO_PhanCongDT> PCDT_Select_MaGV_QLy_SVDaLam(string magv)
        {
            List<DTO_PhanCongDT> listPhancongdt = new List<DTO_PhanCongDT>();
            try
            {
                string s = "Proc_PCDT_Select_MaGV_QLy_SVDaLam";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = ketnoi.Cmb();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = s;
                cmd.Parameters.Add("@MaGV", SqlDbType.NVarChar).Value = magv;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DTO_PhanCongDT pcdt = new DTO_PhanCongDT();
                    pcdt.ID_PCDT = int.Parse(dt.Rows[i]["ID_PCDT"].ToString());
                    pcdt.MaDT = dt.Rows[i]["MaDT"].ToString();
                    pcdt.MaSV = dt.Rows[i]["MaSV"].ToString();
                    pcdt.MaCN = dt.Rows[i]["MaCN"].ToString();
                    pcdt.MaKH = dt.Rows[i]["MaKH"].ToString();
                    pcdt.MaLoaiDT = dt.Rows[i]["MaLoaiDT"].ToString();
                    pcdt.NgayNhan = DateTime.Parse(dt.Rows[i]["NgayNhan"].ToString());
                    pcdt.NgayHetHan = DateTime.Parse(dt.Rows[i]["NgayHetHan_ChonDT"].ToString());
                    pcdt.DiemNHD = float.Parse(dt.Rows[i]["DiemNHD"].ToString());
                    pcdt.DiemPB = float.Parse(dt.Rows[i]["DiemPB"].ToString());
                    pcdt.DiemTB = (float)((pcdt.DiemNHD * 40) / 100) + (float)((pcdt.DiemPB * 60) / 100);
                    pcdt.TrangThai = bool.Parse(dt.Rows[i]["TrangThai"].ToString());
                    pcdt.TenDT = dt.Rows[i]["TenDT"].ToString();
                    pcdt.TenGV = dt.Rows[i]["TenGV"].ToString();
                    pcdt.TenCN = dt.Rows[i]["TenCN"].ToString();
                    pcdt.TenloaiDT = dt.Rows[i]["TenloaiDT"].ToString();
                    pcdt.NamBD = dt.Rows[i]["NamBD"].ToString();
                    pcdt.NamKT = dt.Rows[i]["NamKT"].ToString();
                    pcdt.Sotuan = int.Parse(dt.Rows[i]["SoTuan"].ToString());
                    listPhancongdt.Add(pcdt);
                }
                ketnoi.Cmb().Close();
                return listPhancongdt;
            }
            catch (Exception ex)
            {
                return listPhancongdt;
            }
        }

        public static List<DTO_PhanCongDT> PCDT_Select_Chamdiem(string makhoahoc,string malop,string maloaidt,bool dalam_chualam)
        {
            List<DTO_PhanCongDT> listPhancongdt = new List<DTO_PhanCongDT>();
            try
            {
                string s="";
                if(dalam_chualam)
                    s = "proc_DeTaiDaLam";
                else
                    s = "proc_DeTaiDangLam";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = ketnoi.Cmb();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = s;
                cmd.Parameters.Add("@MaKH", SqlDbType.NVarChar).Value = makhoahoc;
                cmd.Parameters.Add("@MaLop", SqlDbType.NVarChar).Value = malop;
                cmd.Parameters.Add("@LoaiDT", SqlDbType.NVarChar).Value = maloaidt;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DTO_PhanCongDT pcdt = new DTO_PhanCongDT();
                    pcdt.ID_PCDT = int.Parse(dt.Rows[i]["ID_PCDT"].ToString());
                    pcdt.MaDT = dt.Rows[i]["MaDT"].ToString();
                    pcdt.MaSV = dt.Rows[i]["MaSV"].ToString();
                    pcdt.MaCN = dt.Rows[i]["MaCN"].ToString();
                    pcdt.MaKH = dt.Rows[i]["MaKH"].ToString();
                    pcdt.MaLoaiDT = dt.Rows[i]["MaLoaiDT"].ToString();
                    pcdt.NgayNhan = DateTime.Parse(dt.Rows[i]["NgayNhan"].ToString());
                    pcdt.NgayHetHan = DateTime.Parse(dt.Rows[i]["NgayHetHan_ChonDT"].ToString());
                    pcdt.DiemNHD = float.Parse(dt.Rows[i]["DiemNHD"].ToString());
                    pcdt.DiemPB = float.Parse(dt.Rows[i]["DiemPB"].ToString());
                    pcdt.DiemTB = (float)((pcdt.DiemNHD * 40) / 100) + (float)((pcdt.DiemPB * 60) / 100);
                    pcdt.TrangThai = bool.Parse(dt.Rows[i]["TrangThai"].ToString());
                    pcdt.MaGV = dt.Rows[i]["MaGV"].ToString();
                    pcdt.TenGV = dt.Rows[i]["TenGV"].ToString();
                    listPhancongdt.Add(pcdt);
                }
                ketnoi.Cmb().Close();
                return listPhancongdt;
            }
            catch (Exception ex)
            {
                return listPhancongdt;
            }
        }
        /////////////////////////////////////////////////CHAM DIEM CUA GV//////////////////////////////////////////////////////
        public static List<DTO_PhanCongDT> PCDT_Select_Chamdiem_GV(string magv,string makhoahoc, string malop, string maloaidt, bool dalam_chualam)
        {
            List<DTO_PhanCongDT> listPhancongdt = new List<DTO_PhanCongDT>();
            try
            {
                string s = "";
                if (dalam_chualam)
                    s = "proc_DeTaiDaLam_GV";
                else
                    s = "proc_DeTaiDangLam_GV";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = ketnoi.Cmb();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = s;
                cmd.Parameters.Add("@MaGV", SqlDbType.NVarChar).Value = magv;
                cmd.Parameters.Add("@MaKH", SqlDbType.NVarChar).Value = makhoahoc;
                cmd.Parameters.Add("@MaLop", SqlDbType.NVarChar).Value = malop;
                cmd.Parameters.Add("@LoaiDT", SqlDbType.NVarChar).Value = maloaidt;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DTO_PhanCongDT pcdt = new DTO_PhanCongDT();
                    pcdt.ID_PCDT = int.Parse(dt.Rows[i]["ID_PCDT"].ToString());
                    pcdt.MaDT = dt.Rows[i]["MaDT"].ToString();
                    pcdt.MaSV = dt.Rows[i]["MaSV"].ToString();
                    pcdt.MaCN = dt.Rows[i]["MaCN"].ToString();
                    pcdt.MaKH = dt.Rows[i]["MaKH"].ToString();
                    pcdt.MaLoaiDT = dt.Rows[i]["MaLoaiDT"].ToString();
                    pcdt.NgayNhan = DateTime.Parse(dt.Rows[i]["NgayNhan"].ToString());
                    pcdt.NgayHetHan = DateTime.Parse(dt.Rows[i]["NgayHetHan_ChonDT"].ToString());
                    pcdt.DiemNHD = float.Parse(dt.Rows[i]["DiemNHD"].ToString());
                    pcdt.DiemPB = float.Parse(dt.Rows[i]["DiemPB"].ToString());
                    pcdt.TrangThai = bool.Parse(dt.Rows[i]["TrangThai"].ToString());
                    pcdt.TenDT = dt.Rows[i]["TenDT"].ToString();
                    pcdt.TenGV = dt.Rows[i]["TenGV"].ToString();
                    pcdt.TenCN = dt.Rows[i]["TenCN"].ToString();
                    pcdt.TenloaiDT = dt.Rows[i]["TenloaiDT"].ToString();
                    pcdt.NamBD = dt.Rows[i]["NamBD"].ToString();
                    pcdt.NamKT = dt.Rows[i]["NamKT"].ToString();
                    pcdt.Sotuan = int.Parse(dt.Rows[i]["SoTuan"].ToString());
                    
                    listPhancongdt.Add(pcdt);
                }
                ketnoi.Cmb().Close();
                return listPhancongdt;
            }
            catch (Exception ex)
            {
                return listPhancongdt;
            }
        }
     


    }
}
